<?php

namespace MemLib;

/**
 * 数据库执行类
 * @create 张云江
 */

define("QUERY_KEY", 1);
define("QUERY_INDEX", 2);
define("QUERY_BOTH", 3);
define('DB_LIST_BY_USER_ID', 1);
define('DB_LIST_BY_PES_ID', 2);

class Pdb
{
    private $host;
    private $username;
    private $pwd;
    private $port;
    private $databasename;
    private $db;
    private $charset  = "utf8";
    private $log_data = '';

    //解决重复链接的问题
    private static $db_handler = null;

    /** 线下实训/线上实训（网课） */
    const db_order = "db_order";
    const db_auth = "db_auth";
    /**
     * 构造函数
     * @param string key_back or user_id     
     * 
     */
    function __construct($key_back, $db_type = null)
    {
        $databaseinfo = \Config\DbConfig::getConfig($key_back);
        if (is_array($databaseinfo)) {
            $this->host = $databaseinfo["host"];
            $this->username = $databaseinfo["username"];
            $this->pwd = $databaseinfo["pwd"];
            $this->port = $databaseinfo["port"];
            $this->databasename = $databaseinfo["databasename"];
            if ($databaseinfo["charset"]) {
                $this->charset = $databaseinfo["charset"];
            }
        } else {
            if (class_exists("log")) {
                \Mlog::info("未解析到数据库连接信息,key_back:" . $key_back);
            }
            throw new \Exception("根据该user_id解析不到相应数据库，服务器配置的ID是1-105000000，请确认user_id是否在该范围内，如果问题任然无法解决，联系接口开发人员");
        }
    }

    /**
     * 析构函数
     */
    function __destruct()
    {
        if (!empty($this->log_data)) {
            \Mlog::info($this->log_data);
        }
    }

    /**
     * 获取数据库对象
     * @throws \Exception 
     * @return \mysqli
     */
    public function getDb()
    {
        //添加日志
        if (class_exists("log")) {
            $this->log_data .= Log::formatData('server:' . $this->host);
            $this->log_data .= Log::formatData('db_name:' . $this->databasename);
        }
        $_db_handle = $this->getDb_handler();
        $key = $this->host . "_" . $this->port;
        if (is_null($_db_handle[$key])) {
            $_db_handle[$key] = mysqli_connect($this->host, $this->username, $this->pwd, $this->databasename, $this->port) or die("db Could not connect: " . mysqli_connect_errno() . "<br/>");
            $_db_handle[$key]->query("SET names $this->charset");
            $this->setDb_handler($_db_handle);
        } else {

        }
        //改变database
        $resu = mysqli_select_db($_db_handle[$key], $this->databasename);
        if (!$resu) {
            Log::log_trace("改变DB出错:" . print_r($this->getError(), true), "pdb");
            Log::logWrite("改变DB出错:" . print_r($this->getError(), true), "db_error", "db_error_" . date("Ymd"), LOG_ERROR);
            $_db_handle[$key]->close();
            $_db_handle[$key] = null;
            $this->setDb_handler($_db_handle);
        }
        return $_db_handle[$key];
    }
    /**
     * 获取自增id，用于调用insert方法之后获取自增id
     */
    public function getAutoIncreId()
    {
        $db = $this->getDb();
        return $db->insert_id;
    }
    public function getRow($sql)
    {
        $db = $this->getDb();
        $result = $db->query($sql);
        $row = $result->fetch_array();
        $this->record_db_error($sql);
        return $row;
    }

    /**
     * 执行sql语句，返回结果集
     * @param mixed $sql sql语句
     * @return mixed  $result 返回结果集
     */
    public function getResult($sql)
    {
        $db = $this->getDb();
        $result = $db->query($sql);
        $this->record_db_error($sql);
        return $result;
    }

    /**
     * 执行sql语句
     * @param mixed $sql sql语句
     * @return  boolean
     */
    public function ExecSql($sql)
    {
        if (class_exists("log"))
            $this->log_data .= Log::formatData($sql);
        $db = $this->getDb();
        $result = $db->multi_query($sql);
        $this->record_db_error($sql);
        return $result;
    }

    /**
     *增加数据  默认insert方法实现
     * @param string $tableName 
     * @param array $data  eg:array("field1"=>"data1","field2"=>"data2",)
     * @param boolean $is_replace 
     * 是否采用replace方法插入数据
     * @return  boolean
     */
    public function AddData($table_name, $data, $is_replace = false)
    {
        $values = "";
        $fields = "";
        foreach ($data as $key => $val) {
            if (!is_numeric($key)) {  //不可能用数字做字段名
                $fields .= $key . ",";
            }
            $values .= "'" . addslashes($val) . "',";
        }
        $fields = substr($fields, 0, -1);
        $values = substr($values, 0, -1);
        if (strlen($fields) < 5) {
            $sql = " INTO   $table_name values($values)";
        } else {
            $sql = " INTO   $table_name($fields) values($values)";
        }
        $sql = $is_replace ? "REPLACE " . $sql : "INSERT " . $sql;
        $result = $this->ExecSql($sql);
        return $result;
    }

    /**
     *获取数据执行错误
     * @return  string
     */
    public function getError()
    {
        $db = self::$db_handler;
        return $db[$this->host . "_" . $this->port]->error;
    }

    /**
     * 记录数据库错误
     */
    private function record_db_error($sql)
    {
        $db_error = $this->getError();
        if (!empty($db_error)) { //记录数据库错误
            \Mlog::info("错误sql:" . $sql . ";error:" . $db_error);
            throw new \Exception($db_error);
        }
    }
    /**
     * 执行sql语句，返回结果集
     * @param mixed $sql sql语句
     * @return  $datatable array(0=>array("id"=>1),1=>array("id"=>2))
     */
    public function returnDataTable($sql, $type = 3)
    {
        if (class_exists("log"))
            $this->log_data .= Log::formatData($sql);
        $db = $this->getDb();
        if (!$db) {
            #连接DB失败 ，允许重试3次
            for ($i = 0; $i < 3; $i++) {
                $db = $this->getDb();
                if ($db)
                    break;
            }
        }
        if ($db) {
            $sqlSuccess = $db->multi_query($sql);
            if ($sqlSuccess) {
                $i = 0;
                $dataTable = array();
                do {
                    /* store first result set */
                    if ($result = $db->store_result()) {
                        $row = $result->fetch_array(MYSQLI_ASSOC);
                        while ($row) {
                            $dataTable[$i] = $row;
                            $i++;
                            $row = $result->fetch_array(MYSQLI_ASSOC);
                        }
                        $result->free();
                    }
                    if (!$db->more_results()) break;
                    $i++;
                } while ($db->more_results() && $db->next_result());
                $this->record_db_error($sql);
                return $dataTable;
            } else {
                $this->record_db_error($sql);
                return array();
            }
        }
    }
    public function returnDataTables($sql, $type = 3)
    {
        $this->log_data .= Log::formatData($sql);
        $db = $this->getDb();
        if ($db->multi_query($sql)) {
            $j = 0;
            do {
                $result = $db->store_result();
                if ($result) {
                    //获取第一个结果集                    
                    $row = $result->fetch_array($type);
                    $i = 0;
                    while ($row) {
                        $datatable[$j][$i] = $row;
                        $i++;
                        $row = $result->fetch_array($type);
                    }
                    $result->close();//关闭一个打开的结果集    
                }
                $j++;
            } while ($db->more_results() && $db->next_result());
        }
        @mysqli_free_result($result);
        $this->record_db_error($sql);
        return $datatable;
    }

    public function getProcSql($procname, $params = NULL)
    {
        $sql = "call " . $procname . "(";
        if ($params) {
            $sqlOutPut = "select ";
            for ($i = 0; $i < count($params); $i++) {
                // if ($params[$i]->Direction == Direction::$Output) {
                //     $sql .= $params[$i]->SqlParamName;
                //     $sqlOutPut = $sqlOutPut . $params[$i]->SqlParamName . ",";
                //     $sql .= ",";
                // } else if ($params[$i]->Direction == Direction::$Intput) {
                //     $sql .= "'" . $params[$i]->ParamValue . "',";
                // }
            }
            if (count($params) > 0) {
                $sql = substr($sql, 0,  strlen($sql) - 1) . ");";
                $sqlOutPut = substr($sqlOutPut, 0,  strlen($sqlOutPut) - 1) . ";";
            }
        } else {
            $sql .= ");";
        }
        if (strlen($sqlOutPut) > 7) {
            $sql .= $sqlOutPut;
        }
        $this->log_data .= Log::formatData($sql);
        return $sql;
    }

    public function runProcReturnTable($procname, $params = NULL, $type = QUERY_KEY)
    {
        $sql = $this->getProcSql($procname, $params);
        return $this->returnDataTable($sql);
    }

    private function setDb_handler($db_handler)
    {
        self::$db_handler = $db_handler;
    }

    private function getDb_handler()
    {
        return self::$db_handler;
    }

    /**
     * 执行存储过程
     * @param string     存储过程名称
     * @param array       参数数组 array(0=>SqlParam)
     * @param db           存储连接DB
     * @return string      返回构建的sql语句
     */
    private function serializationProc($procname, $params, &$db)
    {
        $sql = "call " . $procname . "(";
        if (count($params) > 0) {
            $sqlOutPut = "select ";
            foreach ($params as $v) {
                // if ($v->Direction == Direction::$ReturnValue) {
                //     continue;
                // }
                if (strpos($v->SqlParamName, "@") === FALSE) {
                    $v->SqlParamName = "@" . $v->SqlParamName;
                }
                $db->query("set " . $v->SqlParamName . "='" . $v->ParamValue . "';");
                PublicFunc::_print("set " . $v->SqlParamName . "='" . $v->ParamValue . "';");
                $sql .= $v->SqlParamName;
                $sql .= ",";
                // if ($v->Direction == Direction::$Output) {
                //     $sqlOutPut .= $v->SqlParamName . ",";
                // }
            }
            if (count($params) > 0) {
                $sql = substr($sql, 0,  strlen($sql) - 1) . ");";
                $sqlOutPut = substr($sqlOutPut, 0,  strlen($sqlOutPut) - 1) . ";";
            }
        } else {
            $sql .= ");";
        }
        if (strlen($sqlOutPut) > 7) {
            $sql .= $sqlOutPut;
        }
        $this->log_data .= Log::formatData($sql);
        PublicFunc::_print($sql);
        return $sql;
    }

    function echoConn()
    {
        echo "Class Name:", get_class($this), "<br/>";
        echo "functions:<br/>";
        $class_methods = get_class_methods(get_class($this));
        foreach ($class_methods as $method_name) {
            echo $method_name, ",";
        }
        echo "<br/>";
        echo "host:", $this->host, "<br/>";
        echo "userName:", $this->username, "<br/>";
        echo "pwd:", $this->pwd, "<br/>";
        echo "databasename:", $this->databasename, "<br/>";
    }
}
